package model;

import java.sql.*;
import java.util.*;

public class CommunityLinkDAO {
	
	/**
	 * 커뮤니티 링크 입력
	 * */	
	public int insert(CommunityLink cl){
		int result = 0;
		Connection conn = null;
		String sql = "insert into communitylink (" +
				"cono, linkname, linkaddress" +
				") values (?,?,?)";
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, cl.getCono());
			pstmt.setString(2, cl.getLinkname());
			pstmt.setString(3, cl.getLinkaddress());
			
			result = pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		
		return result;
	}

	/**
	 * 커뮤니티 링크 삭제
	 * */	
	public int delete(CommunityLink cl){
		int result = 0;
		Connection conn = null;
		String sql = "delete from communitylink  where linkno=?";
		
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, cl.getLinkno());
			
			result = pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		
		return result;
	}
	
	public int delete(int cono){
		int result = 0;
		Connection conn = null;
		String sql = "delete from communitylink  where cono=?";
		
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, cono);
			
			result = pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		
		return result;
	}
	
	/**
	 * 커뮤니티 링크 수정
	 * */	
	public int update(CommunityLink cl){
		int result = 0;
		Connection conn = null;
		String sql = "update communitylink set " +
				"linkname=?, linkaddress=? where linkno=?";
		
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, cl.getLinkname());
			pstmt.setString(2, cl.getLinkaddress());
			pstmt.setInt(3, cl.getLinkno());
			
			result = pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		
		return result;
	}
	
	/**
	 * 커뮤니티별 링크 목록 추출
	 * */
	public Vector<CommunityLink> getCommunityLinks(int cono){
		Vector<CommunityLink> v = new Vector<CommunityLink>();
		String sql = "select linkno, cono, linkname, linkaddress from communitylink where cono=?";
		Connection conn = null;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, cono);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				CommunityLink cl = new CommunityLink();
				cl.setLinkno(rs.getInt("linkno"));
				cl.setCono(rs.getInt("cono"));
				cl.setLinkname(rs.getString("linkname"));
				cl.setLinkaddress(rs.getString("linkaddress"));
				v.add(cl);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		
		return v;
	}
	
	public static void main(String[] args){
		CommunityLinkDAO cld = new CommunityLinkDAO();
		
//		System.out.println(cld.getCommunityLinks(1).get(0).getLinkno());
//		System.out.println(cld.getCommunityLinks(1).get(0).getLinkname());
//		System.out.println(cld.getCommunityLinks(1).get(0).getLinkaddress());
		
		CommunityLink cl = new CommunityLink();
		cl.setCono(1);
		cl.setLinkname("naver");
		cl.setLinkaddress("www.nave.com");
		System.out.println(cld.insert(cl));
		
//		CommunityLink cl = new CommunityLink();
//		cl.setLinkno(1);
//		cl.setLinkname("Naver");
//		cl.setLinkaddress("http://www.naver.com");
//		System.out.println(cld.update(cl));
		
//		CommunityLink cl = new CommunityLink();
//		cl.setLinkno(1);
//		System.out.println(cld.delete(cl));
		
	}
	
}
